<!DOCTYPE html>
<html>
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="utf-8">
  
  <title>SqlServer中的HierarchyId数据类型 | gdme1320 的笔记</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="Table of Contents   Query Example  基本查询，ToString函数 通过HierarchyId值查询数据 获取上级结点函数 获取子结点 获取直接下级   Insert Example  创建新数据库和测试数据       Query Example   查询数据库为AdventureWorks2008R2_Data: http://msftdbprodsam">
<meta property="og:type" content="article">
<meta property="og:title" content="SqlServer中的HierarchyId数据类型">
<meta property="og:url" content="http://gdme1320.gitee.com/mssql/hierarchyid-datatype/index.html">
<meta property="og:site_name" content="gdme1320 的笔记">
<meta property="og:description" content="Table of Contents   Query Example  基本查询，ToString函数 通过HierarchyId值查询数据 获取上级结点函数 获取子结点 获取直接下级   Insert Example  创建新数据库和测试数据       Query Example   查询数据库为AdventureWorks2008R2_Data: http://msftdbprodsam">
<meta property="og:locale" content="default">
<meta property="og:updated_time" content="2022-03-23T09:59:03.375Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="SqlServer中的HierarchyId数据类型">
<meta name="twitter:description" content="Table of Contents   Query Example  基本查询，ToString函数 通过HierarchyId值查询数据 获取上级结点函数 获取子结点 获取直接下级   Insert Example  创建新数据库和测试数据       Query Example   查询数据库为AdventureWorks2008R2_Data: http://msftdbprodsam">
  
    <link rel="alternate" href="/atom.xml" title="gdme1320 的笔记" type="application/atom+xml">
  
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  

  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" integrity="sha384-1q8mTJOASx8j1Au+a5WDVnPi2lkFfwwEAa8hDDdjZlpLegxhjVME1fgjWPGmkzs7" crossorigin="anonymous">

  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/font-awesome/4.5.0/css/font-awesome.min.css" integrity="sha384-XdYbMnZ/QjLh6iI4ogqCTaIjrFk87ip+ekIjefZch0Y+PvJ8CDYtEs1ipDmPorQ+" crossorigin="anonymous">

  <link rel="stylesheet" href="/css/styles.css">
  <link rel="stylesheet" href="/css/docs.min.css">
  

</head>
</html>
<body>
  <nav class="navbar navbar-inverse">
  <div class="container">
    <!-- Brand and toggle get grouped for better mobile display -->
    <div class="navbar-header">
      <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#main-menu-navbar" aria-expanded="false">
        <span class="sr-only">Toggle navigation</span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
        <span class="icon-bar"></span>
      </button>
      
    </div>

    <!-- Collect the nav links, forms, and other content for toggling -->
    <div class="collapse navbar-collapse" id="main-menu-navbar">
      <ul class="nav navbar-nav">
        
          <li><a class href="/index.html">Home</a></li>
        
          <li><a class href="/archives/">Archives</a></li>
        
      </ul>

      <!--
      <ul class="nav navbar-nav navbar-right">
        
          <li><a href="/atom.xml" title="RSS Feed"><i class="fa fa-rss"></i></a></li>
        
      </ul>
      -->
    </div><!-- /.navbar-collapse -->
  </div><!-- /.container-fluid -->
</nav>

  <div class="container bs-docs-container">
  
    <div class="row">
        <div class="col-sm-8 blog-main">
          <article id="post-mssql/hierarchyid-datatype" class="article article-type-post" itemscope itemprop="blogPost">

  <header class="article-header">
    
  
    <h1 class="article-title" itemprop="name">
      SqlServer中的HierarchyId数据类型
    </h1>
  


  </header>

  <div class="article-meta">
    <div class="article-datetime">
  <a href="/mssql/hierarchyid-datatype/" class="article-date"><time datetime="1970-01-01T00:00:02.016Z" itemprop="datePublished">1970-01-01</time></a>
</div>

    
    
  <div class="article-category">
    <a class="article-category-link" href="/categories/mssql/">mssql</a>
  </div>


  </div>
  <div class="article-inner">

    <div class="article-entry" itemprop="articleBody">
      
        
<div id="content">
<div id="table-of-contents">
<h2>Table of Contents</h2>
<div id="text-table-of-contents">
<ul>
<li><a href="#orgdaddb51">Query Example</a>
<ul>
<li><a href="#orgc8bc6e8">基本查询，ToString函数</a></li>
<li><a href="#org010907b">通过HierarchyId值查询数据</a></li>
<li><a href="#org1cfff7f">获取上级结点函数</a></li>
<li><a href="#orgdfd9539">获取子结点</a></li>
<li><a href="#org7bd24f2">获取直接下级</a></li>
</ul>
</li>
<li><a href="#org8115951">Insert Example</a>
<ul>
<li><a href="#orgf62edf4">创建新数据库和测试数据</a></li>
</ul>
</li>
</ul>
</div>
</div>
<div class="outline-2" id="outline-container-orgdaddb51">
<h2 id="orgdaddb51">Query Example</h2>
<div class="outline-text-2" id="text-orgdaddb51">
<p>
查询数据库为AdventureWorks2008R2_Data: <a href="http://msftdbprodsamples.codeplex.com/releases/view/59211" target="_blank" rel="noopener">http://msftdbprodsamples.codeplex.com/releases/view/59211</a>
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold; font-style: italic;">-- 一共290条记录</span>
<span style="font-weight: bold;">select</span> <span style="font-weight: bold;">count</span>(*) <span style="font-weight: bold;">from</span> HumanResources.Employee;
</pre>
</div>
</div>
<div class="outline-3" id="outline-container-orgc8bc6e8">
<h3 id="orgc8bc6e8">基本查询，ToString函数</h3>
<div class="outline-text-3" id="text-orgc8bc6e8">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold; font-style: italic;">--HumanResources.Employee.OrganizationLevel列的</span>
<span style="font-weight: bold; font-style: italic;">--计算列规范为([OrganizationNode].[GetLevel]())</span>
<span style="font-weight: bold;">select</span> 
e.businessEntityID,
p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> person.person p <span style="font-weight: bold;">on</span> e.BusinessEntityID = p.BusinessEntityID
<span style="font-weight: bold;">where</span> e.OrganizationLevel <span style="font-weight: bold;">Between</span> 1 <span style="font-weight: bold;">and</span> 2
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org010907b">
<h3 id="org010907b">通过HierarchyId值查询数据</h3>
<div class="outline-text-3" id="text-org010907b">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">declare</span> @hierarchyValue HierarchyId
<span style="font-weight: bold;">select</span> @hierarchyValue=e.OrganizationNode <span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.businessEntityID=234;
<span style="font-weight: bold;">select</span> * <span style="font-weight: bold;">from</span> HumanResources.Employee <span style="font-weight: bold;">where</span> HumanResources.Employee.OrganizationNode=@hierarchyValue;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org1cfff7f">
<h3 id="org1cfff7f">获取上级结点函数</h3>
<div class="outline-text-3" id="text-org1cfff7f">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">select</span> e.BusinessEntityID,
p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>,
e.OrganizationNode.GetAncestor(0).ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Self</span>,
e.OrganizationNode.GetAncestor(1).ToString() <span style="font-weight: bold;">as</span> OneUp,
e.OrganizationNode.GetAncestor(2).ToString() <span style="font-weight: bold;">as</span> TwoUp,
e.OrganizationNode.GetAncestor(3).ToString() <span style="font-weight: bold;">as</span> TooFar
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
<span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityID
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3;

<span style="font-weight: bold; font-style: italic;">--获取person Tamburello的所有上级主管（获取所有上级节点）</span>
<span style="font-weight: bold; font-style: italic;">--childNode.IsDescendantOf(parentNode) 判断childNode是否为parentNode的子节点</span>
<span style="font-weight: bold; font-style: italic;">--查询结果会包括Tamburello自身。</span>
<span style="font-weight: bold; font-style: italic;">--因为一个几点可以认为自己既是自己的上级节点（层级为），也能是自己的子节点。</span>
<span style="font-weight: bold;">declare</span> @ChildNode HierarchyID
<span style="font-weight: bold;">select</span> @ChildNode=OrganizationNode
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3

<span style="font-weight: bold;">select</span> e.BusinessEntityID,
        p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
        e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
        <span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityID
<span style="font-weight: bold;">where</span> @ChildNode.IsDescendantOf(e.OrganizationNode)=1;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-orgdfd9539">
<h3 id="orgdfd9539">获取子结点</h3>
<div class="outline-text-3" id="text-orgdfd9539">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">declare</span> @ChildNode HierarchyID
<span style="font-weight: bold;">select</span> @ChildNode=OrganizationNode
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3

<span style="font-weight: bold;">select</span> e.BusinessEntityID,
p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
e.OrganizationNode.ToString() <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">From</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
<span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityID
<span style="font-weight: bold;">where</span> e.OrganizationNode.IsDescendantOf(@ChildNode)=1;
</pre>
</div>
</div>
</div>
<div class="outline-3" id="outline-container-org7bd24f2">
<h3 id="org7bd24f2">获取直接下级</h3>
<div class="outline-text-3" id="text-org7bd24f2">
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">declare</span> @ChildNode hierarchyID
<span style="font-weight: bold;">select</span> @ChildNode=OrganizationNode
<span style="font-weight: bold;">from</span> HumanResources.Employee e
<span style="font-weight: bold;">where</span> e.BusinessEntityID=3;

<span style="font-weight: bold;">select</span> e.BusinessEntityID,
        <span style="font-weight: bold;">Left</span>((p.LastName+<span style="font-style: italic;">', '</span>+p.FirstName),30) <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Name</span>,
        <span style="font-weight: bold;">Left</span>(e.OrganizationNode.ToString(),10) <span style="font-weight: bold;">as</span> <span style="font-weight: bold;">Hierarchy</span>
<span style="font-weight: bold;">From</span> HumanResources.Employee e
<span style="font-weight: bold;">join</span> Person.Person p
<span style="font-weight: bold;">on</span> e.BusinessEntityID=p.BusinessEntityId
<span style="font-weight: bold;">where</span> e.OrganizationNode.GetAncestor(1)=@ChildNode;
</pre>
</div>
</div>
</div>
</div>
<div class="outline-2" id="outline-container-org8115951">
<h2 id="org8115951">Insert Example</h2>
<div class="outline-text-2" id="text-org8115951">
</div>
<div class="outline-3" id="outline-container-orgf62edf4">
<h3 id="orgf62edf4">创建新数据库和测试数据</h3>
<div class="outline-text-3" id="text-orgf62edf4">
<div class="org-src-container">
<pre class="src src-sql">use [Test]
<span style="font-weight: bold;">create</span> <span style="font-weight: bold;">table</span> <span style="font-weight: bold;">NodeTest</span>(
NodeID <span style="font-weight: bold; text-decoration: underline;">int</span> <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span> <span style="font-weight: bold;">identity</span>(1,1) <span style="font-weight: bold;">primary</span> <span style="font-weight: bold;">key</span>,
Node hierarchyid <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span>,
NodeLevel <span style="font-weight: bold;">as</span> Node.GetLevel(),
<span style="font-weight: bold;">Name</span> <span style="font-weight: bold; text-decoration: underline;">varchar</span>(50) <span style="font-weight: bold;">not</span> <span style="font-weight: bold;">null</span>
)

<span style="font-weight: bold; font-style: italic;">--插入根节点，新的id为</span>
<span style="font-weight: bold;">insert</span> NodeTest <span style="font-weight: bold;">values</span>(<span style="font-style: italic;">'/'</span>,<span style="font-style: italic;">'Manager'</span>);
<span style="font-weight: bold; font-style: italic;">--获取id为的数据（获取根节点）</span>
<span style="font-weight: bold;">declare</span> @Manager hierarchyid
<span style="font-weight: bold;">select</span> @Manager = node <span style="font-weight: bold;">from</span> NodeTest <span style="font-weight: bold;">where</span> NodeId=1;
<span style="font-weight: bold;">insert</span> NodeTest <span style="font-weight: bold;">values</span>
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'under root 1'</span>),<span style="font-weight: bold; font-style: italic;">--直接放置在根节点下</span>
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'under root 2'</span>),
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-style: italic;">'/1000/'</span>),<span style="font-style: italic;">'less than 1000 1'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下小于的一个节点</span>
(@manager.GetDescendant(<span style="font-weight: bold;">null</span>,<span style="font-style: italic;">'/1000/'</span>),<span style="font-style: italic;">'less than 1000 2'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下小于的一个节点</span>
(@manager.GetDescendant(<span style="font-style: italic;">'/1000/'</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'more than 1000 1'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下大于的一个节点</span>
(@manager.GetDescendant(<span style="font-style: italic;">'/1000/'</span>,<span style="font-weight: bold;">null</span>),<span style="font-style: italic;">'more than 1000 2'</span>),<span style="font-weight: bold; font-style: italic;">--放置在根节点下大于的一个节点</span>
(<span style="font-style: italic;">'/547/'</span>,<span style="font-style: italic;">'ReportFFF'</span>),
(@manager.GetDescendant(<span style="font-style: italic;">'/3/'</span>,<span style="font-style: italic;">'/547/'</span>),<span style="font-style: italic;">'3 - 547'</span>),
(@manager.GetDescendant(<span style="font-style: italic;">'/1/'</span>,<span style="font-style: italic;">'/2/'</span>),<span style="font-style: italic;">'more than 1000 2'</span>),
(@manager.GetDescendant(<span style="font-style: italic;">'/-10/'</span>,<span style="font-style: italic;">'/-1/'</span>),<span style="font-style: italic;">'more than 1000 2'</span>),
(<span style="font-style: italic;">'/547/345/'</span>,<span style="font-style: italic;">'secondLevel AA'</span>),
(<span style="font-style: italic;">'/547/346/'</span>,<span style="font-style: italic;">'secondLevel BB'</span>),
(<span style="font-style: italic;">'/547/345/1/'</span>,<span style="font-style: italic;">'ThirdLevel AA'</span>),
(<span style="font-style: italic;">'/785/294/386/925/'</span>,<span style="font-style: italic;">'RandomEntry'</span>);
</pre>
</div>
<p>
结果：
</p>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-right">
<col class="org-left">
<col class="org-right">
<col class="org-left">
</colgroup>
<tbody>
<tr>
<td class="org-right">1</td>
<td class="org-left">/</td>
<td class="org-right">0</td>
<td class="org-left">Manager</td>
</tr>
<tr>
<td class="org-right">2</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 1</td>
</tr>
<tr>
<td class="org-right">3</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 2</td>
</tr>
<tr>
<td class="org-right">4</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 1</td>
</tr>
<tr>
<td class="org-right">5</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 2</td>
</tr>
<tr>
<td class="org-right">6</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 1</td>
</tr>
<tr>
<td class="org-right">7</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">8</td>
<td class="org-left"><i>547</i></td>
<td class="org-right">1</td>
<td class="org-left">ReportFFF</td>
</tr>
<tr>
<td class="org-right">9</td>
<td class="org-left"><i>4</i></td>
<td class="org-right">1</td>
<td class="org-left">3 - 547</td>
</tr>
<tr>
<td class="org-right">10</td>
<td class="org-left"><i>1.1</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">11</td>
<td class="org-left"><i>-9</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">12</td>
<td class="org-left"><i>547/345</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel AA</td>
</tr>
<tr>
<td class="org-right">13</td>
<td class="org-left"><i>547/346</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel BB</td>
</tr>
<tr>
<td class="org-right">14</td>
<td class="org-left"><i>547/345/1</i></td>
<td class="org-right">3</td>
<td class="org-left">ThirdLevel AA</td>
</tr>
<tr>
<td class="org-right">15</td>
<td class="org-left"><i>785/294/386/925</i></td>
<td class="org-right">4</td>
<td class="org-left">RandomEntry</td>
</tr>
</tbody>
</table>
<p>
执行了去除重复的移动操作，结果如下，（只有两个1001节点，两个1001节点是手动插入的数据）
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">update</span> NodeTest
<span style="font-weight: bold;">set</span> Node=Node.GetReparentedValue(<span style="font-style: italic;">'/547/'</span>,<span style="font-style: italic;">'/1001/'</span>)
<span style="font-weight: bold;">where</span> Node.IsDescendantOf(<span style="font-style: italic;">'/547/'</span>)=1
<span style="font-weight: bold;">select</span> Node.ToString() <span style="font-weight: bold;">from</span> NodeTest;
</pre>
</div>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-right">
<col class="org-left">
<col class="org-right">
<col class="org-left">
</colgroup>
<tbody>
<tr>
<td class="org-right">1</td>
<td class="org-left">/</td>
<td class="org-right">0</td>
<td class="org-left">Manager</td>
</tr>
<tr>
<td class="org-right">2</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 1</td>
</tr>
<tr>
<td class="org-right">3</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 2</td>
</tr>
<tr>
<td class="org-right">4</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 1</td>
</tr>
<tr>
<td class="org-right">5</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 2</td>
</tr>
<tr>
<td class="org-right">6</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 1</td>
</tr>
<tr>
<td class="org-right">7</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">8</td>
<td class="org-left"><i>547</i></td>
<td class="org-right">1</td>
<td class="org-left">ReportFFF</td>
</tr>
<tr>
<td class="org-right">9</td>
<td class="org-left"><i>4</i></td>
<td class="org-right">1</td>
<td class="org-left">3 - 547</td>
</tr>
<tr>
<td class="org-right">10</td>
<td class="org-left"><i>1.1</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">11</td>
<td class="org-left"><i>-9</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">12</td>
<td class="org-left"><i>1001/345</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel AA</td>
</tr>
<tr>
<td class="org-right">13</td>
<td class="org-left"><i>1001/346</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel BB</td>
</tr>
<tr>
<td class="org-right">14</td>
<td class="org-left"><i>1001/345/1</i></td>
<td class="org-right">3</td>
<td class="org-left">ThirdLevel AA</td>
</tr>
<tr>
<td class="org-right">15</td>
<td class="org-left"><i>785/294/386/925</i></td>
<td class="org-right">4</td>
<td class="org-left">RandomEntry</td>
</tr>
</tbody>
</table>
<p>
如果执行不去除重复的移动操作，结果如下
</p>
<p>
注意有3个1001节点，其中两个是手动插入的节点，另外一个是547节点移动后变成为了1001
</p>
<div class="org-src-container">
<pre class="src src-sql"><span style="font-weight: bold;">update</span> NodeTest
<span style="font-weight: bold;">set</span> Node=Node.GetReparentedValue(<span style="font-style: italic;">'/547/'</span>,<span style="font-style: italic;">'/1001/'</span>)
<span style="font-weight: bold;">where</span> Node.IsDescendantOf(<span style="font-style: italic;">'/547/'</span>)=1
<span style="font-weight: bold;">select</span> Node.ToString() <span style="font-weight: bold;">from</span> NodeTest;
</pre>
</div>
<table border="2" cellpadding="6" cellspacing="0" frame="hsides" rules="groups">
<colgroup>
<col class="org-right">
<col class="org-left">
<col class="org-right">
<col class="org-left">
</colgroup>
<tbody>
<tr>
<td class="org-right">1</td>
<td class="org-left">/</td>
<td class="org-right">0</td>
<td class="org-left">Manager</td>
</tr>
<tr>
<td class="org-right">2</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 1</td>
</tr>
<tr>
<td class="org-right">3</td>
<td class="org-left"><i>1</i></td>
<td class="org-right">1</td>
<td class="org-left">under root 2</td>
</tr>
<tr>
<td class="org-right">4</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 1</td>
</tr>
<tr>
<td class="org-right">5</td>
<td class="org-left"><i>999</i></td>
<td class="org-right">1</td>
<td class="org-left">less than 1000 2</td>
</tr>
<tr>
<td class="org-right">6</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 1</td>
</tr>
<tr>
<td class="org-right">7</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">8</td>
<td class="org-left"><i>1001</i></td>
<td class="org-right">1</td>
<td class="org-left">ReportFFF</td>
</tr>
<tr>
<td class="org-right">9</td>
<td class="org-left"><i>4</i></td>
<td class="org-right">1</td>
<td class="org-left">3 - 547</td>
</tr>
<tr>
<td class="org-right">10</td>
<td class="org-left"><i>1.1</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">11</td>
<td class="org-left"><i>-9</i></td>
<td class="org-right">1</td>
<td class="org-left">more than 1000 2</td>
</tr>
<tr>
<td class="org-right">12</td>
<td class="org-left"><i>1001/345</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel AA</td>
</tr>
<tr>
<td class="org-right">13</td>
<td class="org-left"><i>1001/346</i></td>
<td class="org-right">2</td>
<td class="org-left">secondLevel BB</td>
</tr>
<tr>
<td class="org-right">14</td>
<td class="org-left"><i>1001/345/1</i></td>
<td class="org-right">3</td>
<td class="org-left">ThirdLevel AA</td>
</tr>
<tr>
<td class="org-right">15</td>
<td class="org-left"><i>785/294/386/925</i></td>
<td class="org-right">4</td>
<td class="org-left">RandomEntry</td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
<div class="status" id="postamble">
<p class="date">Date: 2016</p>
<p class="author">Author: gdme1320</p>
<p class="validation"><a href="http://validator.w3.org/check?uri=referer" target="_blank" rel="noopener">Validate</a></p>
</div>

      
    </div>

    
      

    

    <footer class="article-footer">
      <a data-url="http://gdme1320.gitee.com/mssql/hierarchyid-datatype/" data-id="cl13eo7kj0099hjv41hqbkxre" class="article-share-link">
        <i class="fa fa-share"></i> Share
      </a>
      
      

    </footer>
  </div>
  
    
<ul id="article-nav" class="nav nav-pills nav-justified">
  
  <li role="presentation">
    <a href="/mssql/snippets/" id="article-nav-older" class="article-nav-link-wrap">
      <i class="fa fa-chevron-left pull-left"></i>
      <span class="article-nav-link-title">SqlServer代码片段</span>
    </a>
  </li>
  
  
  <li role="presentation">
    <a href="/js/jquery-snipets/" id="article-nav-newer" class="article-nav-link-wrap">
      <span class="article-nav-link-title">JQuery Snippets</span>
      <i class="fa fa-chevron-right pull-right"></i>
    </a>
  </li>
  
</ul>


  
</article>




        </div>
        <div class="col-sm-3 col-sm-offset-1 blog-sidebar">
          
  
  <div class="sidebar-module">
    <h4>Categories</h4>
    <ul class="sidebar-module-list"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/apps/">apps</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/architecture/">architecture</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/devops/">devops</a><span class="sidebar-module-list-count">10</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/devops/kubectl/">kubectl</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/devops/kubernetes/">kubernetes</a><span class="sidebar-module-list-count">3</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/dotnet/">dotnet</a><span class="sidebar-module-list-count">3</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/dotnet/entityframework/">entityframework</a><span class="sidebar-module-list-count">1</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/elasticsearch/">elasticsearch</a><span class="sidebar-module-list-count">4</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/emacs/">emacs</a><span class="sidebar-module-list-count">4</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/emacs/org-mode/">org-mode</a><span class="sidebar-module-list-count">3</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/git/">git</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/graphql/">graphql</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/groovy/">groovy</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/">java</a><span class="sidebar-module-list-count">52</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/concurrent/">concurrent</a><span class="sidebar-module-list-count">4</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/jackson/">jackson</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/shiro/">shiro</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/spring/">spring</a><span class="sidebar-module-list-count">11</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/spring-amqp/">spring-amqp</a><span class="sidebar-module-list-count">5</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/spring-amqp/spring-jpa/">spring-jpa</a><span class="sidebar-module-list-count">1</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/spring-jpa/">spring-jpa</a><span class="sidebar-module-list-count">10</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/spring-security/">spring-security</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/thymeleaf/">thymeleaf</a><span class="sidebar-module-list-count">2</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/java/thymeleaf/maven/">maven</a><span class="sidebar-module-list-count">1</span></li></ul></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/jenkins/">jenkins</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/journal/">journal</a><span class="sidebar-module-list-count">7</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/js/">js</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/linux/">linux</a><span class="sidebar-module-list-count">35</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/linux/kde/">kde</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/linux/kross/">kross</a><span class="sidebar-module-list-count">3</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/linux/kross/ktorrent-scripts/">ktorrent-scripts</a><span class="sidebar-module-list-count">1</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/linux/shell/">shell</a><span class="sidebar-module-list-count">6</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/linux/tcp/">tcp</a><span class="sidebar-module-list-count">1</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/lisp/">lisp</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/mongodb/">mongodb</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/mssql/">mssql</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/mysql/">mysql</a><span class="sidebar-module-list-count">12</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/nginx/">nginx</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/node/">node</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/postgresql/">postgresql</a><span class="sidebar-module-list-count">2</span><ul class="sidebar-module-list-child"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/postgresql/client/">client</a><span class="sidebar-module-list-count">1</span></li></ul></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/python/">python</a><span class="sidebar-module-list-count">19</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/regex/">regex</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/rfc/">rfc</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/vim/">vim</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/categories/windows/">windows</a><span class="sidebar-module-list-count">7</span></li></ul>
  </div>



  
  <div class="sidebar-module">
    <h4>Tags</h4>
    <ul class="sidebar-module-list"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/tags/ReentrantLock/">ReentrantLock</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/tags/nohup/">nohup</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/tags/pandoc/">pandoc</a><span class="sidebar-module-list-count">1</span></li></ul>
  </div>



  
  <div class="sidebar-module">
    <h4>Tag Cloud</h4>
    <p class="tagcloud">
      <a href="/tags/ReentrantLock/" style="font-size: 10px;">ReentrantLock</a> <a href="/tags/nohup/" style="font-size: 10px;">nohup</a> <a href="/tags/pandoc/" style="font-size: 10px;">pandoc</a>
    </p>
  </div>


  
  <div class="sidebar-module">
    <h4>Archives</h4>
    <ul class="sidebar-module-list"><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2919/06/">June 2919</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2022/03/">March 2022</a><span class="sidebar-module-list-count">58</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2022/01/">January 2022</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2021/11/">November 2021</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2021/10/">October 2021</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2021/04/">April 2021</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2021/03/">March 2021</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2021/02/">February 2021</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2021/01/">January 2021</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/12/">December 2020</a><span class="sidebar-module-list-count">2</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/11/">November 2020</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/10/">October 2020</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/09/">September 2020</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/07/">July 2020</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/06/">June 2020</a><span class="sidebar-module-list-count">10</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/04/">April 2020</a><span class="sidebar-module-list-count">4</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/03/">March 2020</a><span class="sidebar-module-list-count">8</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/02/">February 2020</a><span class="sidebar-module-list-count">4</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2020/01/">January 2020</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/12/">December 2019</a><span class="sidebar-module-list-count">7</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/11/">November 2019</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/10/">October 2019</a><span class="sidebar-module-list-count">11</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/09/">September 2019</a><span class="sidebar-module-list-count">6</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/08/">August 2019</a><span class="sidebar-module-list-count">4</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/07/">July 2019</a><span class="sidebar-module-list-count">10</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/06/">June 2019</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/05/">May 2019</a><span class="sidebar-module-list-count">3</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/04/">April 2019</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/03/">March 2019</a><span class="sidebar-module-list-count">8</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/02/">February 2019</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2019/01/">January 2019</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2018/11/">November 2018</a><span class="sidebar-module-list-count">4</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2018/10/">October 2018</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2018/08/">August 2018</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2018/07/">July 2018</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2018/06/">June 2018</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2018/03/">March 2018</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2017/07/">July 2017</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2017/06/">June 2017</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2017/04/">April 2017</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/2015/05/">May 2015</a><span class="sidebar-module-list-count">1</span></li><li class="sidebar-module-list-item"><a class="sidebar-module-list-link" href="/archives/1970/01/">January 1970</a><span class="sidebar-module-list-count">9</span></li></ul>
  </div>



  
  <div class="sidebar-module">
    <h4>Recents</h4>
    <ul class="sidebar-module-list">
      
        <li>
          <a href="/java/acessing-generic-types-at-runtime/">获取泛型类型</a>
        </li>
      
        <li>
          <a href="/linux/shell/bash_cgi/">Script for Shell Parsing Parameters</a>
        </li>
      
        <li>
          <a href="/linux/shell/shell-script/">Shell Script Getting Start</a>
        </li>
      
        <li>
          <a href="/linux/tcp/network-tcp-performance-turning-with-sysctl/">【翻译】Sysctl调节Linux网络性能</a>
        </li>
      
        <li>
          <a href="/java/concurrent/aqs-src/">AQS源码笔记</a>
        </li>
      
    </ul>
  </div>



        </div>
    </div>
  </div>
  <footer class="blog-footer">
  <div class="container">
    <div id="footer-info" class="inner">
      &copy; 2022 gdme1320<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>

  

<!-- <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js" integrity="sha384-8gBf6Y4YYq7Jx97PIqmTwLPin4hxIzQw5aDmUg/DDhul9fFpbbLcLh3nTIIDJKhx" crossorigin="anonymous"></script> -->

<script src="/js/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" integrity="sha384-0mSbJDEHialfmuBBQP6A4Qrprq5OVfW37PRR3j5ELqxss1yVqOtnepnHVP9aJ7xS" crossorigin="anonymous"></script>



<script src="/js/script.js"></script>

</body>
</html>
